package com.lovestudying.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;


public class CourseUtil {
	
		
		// 对常量进行封装
		private static final String DRIVER = "com.mysql.jdbc.Driver";
		private static final String URL = "jdbc:mysql://localhost:3306/studyingsystem?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true&useAffectedRows=true&serverTimezone=GMT";
		private static final String USER = "root";
		private static final String PASSWORD = "root";
		
		/**
		 * 封装加载驱动、连接数据库
		 */
		private static Connection getConnection() {
			Connection conn = null;
			try {
				// 加载驱动
				Class.forName(DRIVER);
				
				// 连接数据库
				conn = DriverManager.getConnection(URL, USER, PASSWORD);
				
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
			return conn;
		}
		
		/**
		 * 关闭所有资源
		 */
		public static void closeAll(Connection conn,PreparedStatement ps,ResultSet rs) {
			
			try {
				// 先开的后关
				if(rs != null) {
					rs.close();
				}
				if(ps != null) {
					ps.close();
				}
				if(conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		/**
		 * 进行查询操作
		 */
		public static ResultSet find(String sql,Object...params) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			CachedRowSet rowSet = null;
			
			try {
				// 加载驱动、数据库连接
				conn = getConnection();
				
				// 创建PreparedStatement预编译语句对象
				ps = conn.prepareStatement(sql);
				
				// 当对参数params赋值时
				if(params != null) {
					for (int i = 0; i < params.length; i++) {
						ps.setObject(i + 1 , params[i]);
					}
				}
				
				
				// 进行查询操作
				rs = ps.executeQuery();
				
				// CachedRowSet接口指向实现类CachedRowSetImpl
				rowSet = new CachedRowSetImpl();
				
				// 把结果集传入缓存结果集
				rowSet.populate(rs);
				
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				// 关闭资源
				closeAll(conn, ps, rs);
			}
			return rowSet;
		}
		
		/**
		 * 进行插入、删除、修改操作
		 */
		public static int update(String sql,Object...params) {
			Connection conn = null;
			PreparedStatement ps = null;
			int n = 0;
			
			try {
				// 加载驱动、连接数据路
				conn = getConnection();
				
				// 创建PreparedStatement预编译语句对象
				ps = conn.prepareStatement(sql);
							
				// 当对参数params赋值时
				if(params != null) {
					for (int i = 0; i < params.length; i++) {
						ps.setObject(i + 1 , params[i]);
					}
				}
				
							
				// 进行插入、删除、修改操作
				n = ps.executeUpdate(); 
				
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				closeAll(conn, ps, null);
			}
			return n;
		}
		
		/**
		 * 
		 * @authod Linzhengxiang
		 */
		public static List<Map<String, Object>> ResultSetToList(ResultSet rs) throws SQLException {
			List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
			ResultSetMetaData rsmd = rs.getMetaData();
			int colCount = rsmd.getColumnCount();
			List<String> colNameList = new ArrayList<String>();
			for (int i = 0; i < colCount; i++) {
				colNameList.add(rsmd.getColumnLabel(i + 1));
//				debug(" colNameList = "+rsmd.getColumnLabel(i+1));
			}

			while (rs.next()) {
				Map map = new HashMap<String, Object>();
				for (int i = 0; i < colCount; i++) {

					String key = colNameList.get(i);
					Object value = rs.getString(colNameList.get(i));
//					System.out.println("key = "+key+" value ="+value );
					map.put(key, value);
				}
				results.add(map);
			}
			rs.beforeFirst();
			return results;
		}


	}



